<?php
class Game extends CI_Model {
	
    public function __construct(){
        $this->load->database();
        $this->load->library('factory');
        $this->factory->create('GameTO');
        $this->factory->create('CountryTO');
        $this->factory->create('LeagueTO');
    }
    public function getAllGames(){        
        $sql = "SELECT * FROM game_new";
        $query = $this->db->query($sql);
        $games = array();
        foreach ($query->result() as $row){
            $gameTO = new GameTO();
            $gameTO->setId($row->IDGame);
            $gameTO->setLeague($row->IDLeague);
            $gameTO->setHomeTeam($row->homeTeam);
            $gameTO->setAwayTeam($row->awayTeam);
            $gameTO->setPercentFix1($row->percentFix1);
            $gameTO->setPercentFixX($row->percentFixX);
            $gameTO->setPercentFix2($row->percentFix2);
            $gameTO->setTip($row->tip);
            $gameTO->setOdds1($row->odds1);
            $gameTO->setOddsX($row->oddsX);
            $gameTO->setOdds2($row->odds2);
            $gameTO->setScore1($row->score1);
            $gameTO->setScore2($row->score2);
            $gameTO->setUnder($row->under);
            $gameTO->setOver($row->over);
            $gameTO->setResult($row->result);
            $gameTO->setDateFromPage($row->dateFromPage);
            //$gameTO->setRowNo($row->rowNo);
            $gameTO->setTimestamp($row->timestamp);
            array_push($games, $gameTO);
        }
        return $games;
    }
    public function getAllActiveGamesByIDLeague($IDLeague){        
        $sql = "SELECT * FROM game_new WHERE IDLeague = ".$IDLeague. " AND result =  ' - ' AND DATEDIFF(NOW(), timestamp) < 10 ";
        $query = $this->db->query($sql);
        $games = array();
        foreach ($query->result() as $row){
            $gameTO = new GameTO();
            $gameTO->setId($row->IDGame);
            $gameTO->setLeague($row->IDLeague);
            $gameTO->setHomeTeam($row->homeTeam);
            $gameTO->setAwayTeam($row->awayTeam);
            $gameTO->setPercentFix1($row->percentFix1);
            $gameTO->setPercentFixX($row->percentFixX);
            $gameTO->setPercentFix2($row->percentFix2);
            $gameTO->setTip($row->tip);
            $gameTO->setOdds1($row->odds1);
            $gameTO->setOddsX($row->oddsX);
            $gameTO->setOdds2($row->odds2);
            $gameTO->setScore1($row->score1);
            $gameTO->setScore2($row->score2);
            $gameTO->setUnder($row->under);
            $gameTO->setOver($row->over);
            $gameTO->setResult($row->result);
            $gameTO->setDateFromPage($row->dateFromPage);
            //$gameTO->setRowNo($row->rowNo);
            $gameTO->setTimestamp($row->timestamp);
            array_push($games, $gameTO);
        }
        return $games;
    }    
    // select all finished , order by league 
    public function getAllGamesFiltered(){    		
    	$sql = "SELECT * FROM game_new WHERE result =  ' - ' AND DATEDIFF(NOW(), timestamp) < 10";
    	$query = $this->db->query($sql);
    	$games = array();
    	foreach ($query->result() as $row){
    		$gameTO = new GameTO();
    		$gameTO->setId($row->IDGame);
    		$gameTO->setLeague($row->IDLeague);
    		$gameTO->setHomeTeam($row->homeTeam);
    		$gameTO->setAwayTeam($row->awayTeam);
    		$gameTO->setPercentFix1($row->percentFix1);
    		$gameTO->setPercentFixX($row->percentFixX);
    		$gameTO->setPercentFix2($row->percentFix2);
    		$gameTO->setTip($row->tip);
    		$gameTO->setOdds1($row->odds1);
    		$gameTO->setOddsX($row->oddsX);
    		$gameTO->setOdds2($row->odds2);
    		$gameTO->setScore1($row->score1);
    		$gameTO->setScore2($row->score2);
    		$gameTO->setUnder($row->under);
    		$gameTO->setOver($row->over);
    		$gameTO->setResult($row->result);
    		$gameTO->setDateFromPage($row->dateFromPage);
    		//$gameTO->setRowNo($row->rowNo);
    		$gameTO->setTimestamp($row->timestamp);
    		array_push($games, $gameTO);
    	}
    	return $games;
    }
    
    public function insertGame($game){  
        if($game->getLeague() > 0) {
            $sql = "INSERT INTO game_new VALUES ('','".$game->getHomeTeam()."','".$game->getAwayTeam()."','".$game->getPercentFix1()."','".$game->getPercentFix2()."','".$game->getPercentFixX()."','".$game->getTip()."','".$game->getOdds1()."','".$game->getOdds2()."','".$game->getOddsX()."','".$game->getScore1()."','".$game->getScore2()."','".$game->getUnder()."','".$game->getOver()."','".$game->getResult()."','".$game->getDateFromPage()."','".$game->getTimestamp()."', ".$game->getLeague().", '0000-00-00 00:00:00')";
            $query = $this->db->query($sql);
            echo "<p>SQL: " . $sql . "</p>";
        }
    }
    
    public function updateGame($gameDb,$gameCron){
    	$sql = "UPDATE game SET odds1='".$gameCron->getOdds1()."' ,oddsX='".$gameCron->getOddsX()."' ,odds2='".$gameCron->getOdds2()."' ,result='".$gameCron->getResult()."' WHERE id='".$gameDb->getId()."'";
    	$query = $this->db->query($sql);
        echo "<p>SQL: " . $sql . "</p>";
    }
    
    public function getIdLeagueByShortName($shortName){
        $sql = "SELECT IDLeague FROM league WHERE ShortName = '" . $shortName ."'";
    	$query = $this->db->query($sql);
        if($query->num_rows() == 1){
            foreach ($query->result() as $row){
                return $row->IDLeague;
            }
        }else return 0;
    }
    
    public function saveNewLeagueShortName($newName){
        $sql = "INSERT INTO unused_league_short_name VALUES ('','".$newName."')";
        $this->db->query($sql);
}
    
    public function getLeagueNames(){
        //vraca niz parova LeagueShortName=>IDLeague
        $leagues = array();
        $sql = "SELECT IDLeague, ShortName FROM league";
        $query = $this->db->query($sql);
        foreach ($query->result() as $row){
            $leagues[$row->ShortName] = $row->IDLeague;
        }
        return $leagues;
    }
    
    public function getLeagueByID($IDLeague){
        $sql = "SELECT * FROM league WHERE IDLeague = " . $IDLeague;
        $query = $this->db->query($sql);
        $league = new LeagueTO();
        foreach ($query->result() as $row){
            $league->setShortName($row->ShortName);
            $league->setFullName($row->FullName);
        }
        return $league;
    }
    
    public function getCountries(){
        //veliki niz CountryTO objekata koji drze liste pripadajucih LeagueTo objekata
        //LeagueTO objekti ne drze nikakvu informaciju o pripadajucim utakmicama
        $sql = "SELECT * FROM country ORDER BY popular DESC, FullName ASC";
        $query = $this->db->query($sql);
        $countries = array();
        foreach ($query->result() as $row){
            $countryTO = new CountryTO();
            $countryTO->setIDCountry($row->IDCountry);
            $countryTO->setShortName($row->ShortName);
            $countryTO->setFullName($row->FullName);
            $countryTO->setPopular($row->Popular);
            $countryTO->setVisible($row->visible);
            $countries[$countryTO->getIDCountry()] = $countryTO;
        }
        $sql2 = "SELECT * FROM league";
        $query2 = $this->db->query($sql2);
        foreach ($query2->result() as $row){
            $leagueTO = new LeagueTO();
            $leagueTO->setIDLeague($row->IDLeague);
            $leagueTO->setShortName($row->ShortName);
            $leagueTO->setFullName($row->FullName);
            $leagueTO->setIDCountry($row->IDCountry);
            if($countries[$leagueTO->getIDCountry()]->getVisible() > 0){
                $arr = $countries[$leagueTO->getIDCountry()]->getLeagueList();
                array_push($arr, $leagueTO);
                $countries[$leagueTO->getIDCountry()]->setLeagueList($arr);
            }else{
                unset($countries[$leagueTO->getIDCountry()]);
            }
        }
        return $countries;
    }
    public function getAllLeaguesWithPredictionsByIDCountry($IDCountry){
        $sql = "SELECT league.IDLeague AS IDLeague, 
            league.ShortName AS LeagueShortName,
            league.FullName AS LeagueFullName, 
            league.IDCountry AS IDCountry,
            game_new.IDGame AS IDGame,
            game_new.homeTeam as homeTeam,
            game_new.awayTeam as awayTeam,
            game_new.tip as tip,
            game_new.percentFix1 as percentFix1,
            game_new.percentFixX as percentFixX,
            game_new.percentFix2 as percentFix2
            FROM league INNER JOIN game_new 
            ON (league.IDLeague = game_new.IDLeague) 
            WHERE result =  ' - ' AND DATEDIFF(NOW(), timestamp) < 10 
            AND league.IDCountry = " . $IDCountry . " ORDER BY league.IDLeague ASC";
        $query = $this->db->query($sql);
        $leagues = array();
        
        $currIDLeague = 0;
        foreach ($query->result() as $row){
            
            $gameTO = new GameTO();
            $gameTO->setId($row->IDGame);
            $gameTO->setHomeTeam($row->homeTeam);
            $gameTO->setAwayTeam($row->awayTeam);
            $gameTO->setTip($row->tip);
            $gameTO->setPercentFix1($row->percentFix1);
            $gameTO->setPercentFixX($row->percentFixX);
            $gameTO->setPercentFix2($row->percentFix2);
            $gameTO->setLeague($row->IDLeague);
            
            if($currIDLeague == $row->IDLeague){
                $leagueTO = $leagues[$currIDLeague];
            }
            else{
                $leagueTO = new LeagueTO();
                $leagueTO->setIDLeague($row->IDLeague);
                $leagueTO->setIDCountry($row->IDCountry);
                $leagueTO->setShortName($row->LeagueShortName);
                $leagueTO->setFullName($row->LeagueFullName);
            }
            $currIDLeague = $row->IDLeague;
            $arr = $leagueTO->getGameList();
            array_push($arr, $gameTO);
            $leagueTO->setGameList($arr);
            $leagues[$currIDLeague] = $leagueTO;
        }
        return $leagues;
    }
    
    public function getGameParticipantSynonyme($name){
        $prosoccer_name = '';
        $sql = "SELECT prosoccer_name FROM club_names_connection WHERE betclick_name = ?";
        $query = $this->db->query($sql, array($name));
        foreach ($query->result() as $row){
            $prosoccer_name = $row->prosoccer_name;
        }
        return $prosoccer_name;
    }
    
    public function updateTimesInProsoccerTable($homeTeam, $awayTeam, $timestamp){
        echo "UPDATE game_new SET accurate_start_time = $timestamp WHERE homeTeam = $homeTeam AND awayTeam = $awayTeam AND result LIKE ' - '";
        $sql = "UPDATE game_new SET accurate_start_time = ? WHERE homeTeam = ? AND awayTeam = ? AND result LIKE ' - '";
        $query = $this->db->query($sql, array($timestamp, $homeTeam, $awayTeam));
    }
    
    public function getBettingOffer(){
        //vraca utakmice koje imaju setovan timestamp vremena pocetka 
        //koje je vece od timestamp-a trenutka kada se poziva funkcija
        //utakmice su grupisane po ligama
        //lige su grupisane po zemljama 
        $sql = "SELECT c.IDCountry as IDCountry, c.FullName as CountryFullName, 
            l.IDLeague as IDLeague, l.FullName as LeagueFullName,
            IDGame, accurate_start_time, homeTeam, awayTeam, odds1, oddsX, odds2
            FROM game_new as g INNER JOIN league as l ON g.IDLeague = l.IDLeague INNER JOIN country as c ON l.IDCountry = c.IDCountry
            WHERE accurate_start_time > CURRENT_TIMESTAMP
            ORDER BY IDCountry ASC, IDLeague ASC, accurate_start_time ASC;";
        $query = $this->db->query($sql);
        
        $countries = array();
        $currIDCountry = 0;
        $currIDLeague = 0;
        
        foreach ($query->result() as $row){
            $gameTO = new GameTO();
            $gameTO->setId($row->IDGame);
            $gameTO->setHomeTeam($row->homeTeam);
            $gameTO->setAwayTeam($row->awayTeam);
            $gameTO->setOdds1($row->odds1);
            $gameTO->setOddsX($row->oddsX);
            $gameTO->setOdds2($row->odds2);
            $gameTO->setTimestamp($row->accurate_start_time);
            $gameTO->setLeague($row->IDLeague);
            $gameTO->setDate();
            $gameTO->setTime();
            if($currIDCountry != $row->IDCountry){
                $countryTO = new CountryTO();
                $countryTO->setIDCountry($row->IDCountry);
                $countryTO->setFullName($row->CountryFullName);
                $leagueTO = new LeagueTO();
                $leagueTO->setIDLeague($row->IDLeague);
                $leagueTO->setIDCountry($row->IDCountry);
                $leagueTO->setFullName($row->LeagueFullName);
            }
            if($currIDCountry == $row->IDCountry){
                $countryTO = $countries[$currIDCountry];
                if($currIDLeague == $row->IDLeague){
                    $leagueList = $countryTO->getLeagueList();
                    $leagueTO = $leagueList[$currIDLeague];
                }
                else{
                    $leagueTO = new LeagueTO();
                    $leagueTO->setIDLeague($row->IDLeague);
                    $leagueTO->setIDCountry($row->IDCountry);
                    $leagueTO->setFullName($row->LeagueFullName);
                }
            }
            $gameList = $leagueTO->getGameList();
            array_push($gameList, $gameTO);
            $leagueTO->setGameList($gameList);
            $leagueList = $countryTO->getLeagueList();
            $currIDLeague = $row->IDLeague;
            $currIDCountry = $row->IDCountry;
            $leagueList[$currIDLeague] = $leagueTO;
            $countryTO->setLeagueList($leagueList);
            $countries[$currIDCountry] = $countryTO;
        }
        return $countries;
    }
}
?>